• 28 — The Access Jet Engine
• by Brad Shannon
• DAO Supplied with the Professional Edition versus Standard Edition
• Bound Controls versus the Jet Engine
• Data Access Object Model
• DBEngine Object
• DBEngine Object Properties
• DBEngine Object Methods
• Workspace Object/Collection
• Workspace Object Properties
• Workspace Object Methods
• Database Object/Collection
• Database Object/Collection Properties
• Database Object/Collection Methods
• TableDef Object/Collection
• TableDef Object/Collection Properties
• TableDef Object/Collection Methods
• Field Object/Collection
• Field Object/Collection Properties
• Field Object/Collection Methods
• Index Object/Collection
• Index Object/Collection Properties
• Index Object/Collection Methods
• QueryDef Object/Collection
• QueryDef Object/Collection Properties
• QueryDef Object/Collection Methods
• Parameter Object/Collection
• Parameter Object/Collection Properties
• Parameter Object/Collection Methods
• Recordset Object/Collection
• Recordset Object/Collection Properties
• Recordset Object/Collection Methods
• Relation Object/Collection
• Relation Object/Collection Properties
• Relation Object/Collection Methods
• Error Object/Collection
• Error Object/Collection Properties
• Error Object/Collection Methods
• Summary

28 — The Access Jet Engine

by Brad Shannon

The Jet engine is the database management system used by Visual Basic and Microsoft Access. The Jet engine is responsible for the retrieval and storage of data in users' databases. The Jet engine supplied with Visual Basic comes in the following three forms:

• 16-bit version of Jet version 2.5. This version includes all the new features available in the Jet engine and also provides backwards compatibility with applications created using either Jet 1.1 or Jet 2.0 architecture. Jet 2.5 is compatible with databases created using all version up to and including Access version 2.5.
  
  
• 32-bit version of Jet version 2.5/3.0 compatibility layer. This version includes all the new features available in the Jet engine and also provides backwards compatibility with applications created using either Jet 1.1 or Jet 2.0 architecture. Jet 2.5 is compatible with databases created using all version up to and including Access version 2.5. This 32-bit version is provided to ease the conversion of your applications to Jet 3.0.
  
  
• 32-bit Jet version 3.0. This version is not 100 percent backwards compatible with the previous version of the Jet engine. You may have to modify some areas of your application to take full advantage of the new engine and its features. The Jet 3.0 engine is compatible with databases created using the 32-bit version of Microsoft Access .
  
  

The Jet engine is a collection of objects controlled by your application. The objects are used to access and manipulate information contained in a database used by your application. The collection of objects is referred to as Data Access Objects, or the DAO layer. The following objects make up the DAO layer:

• DBEngine
  
  
• Workspace
  
  
• Database
  
  
• TableDef
  
  
• Field
  
  
• Index
  
  
• QueryDef
  
  
• Parameter
  
  
• Recordset
  
  
• Relation
  
  
• Error
  
  

These objects—and the properties and methods associated with them—are covered in this chapter.

To ensure that the Data Access Objects are available to your project, select the appropriate DAO library (such as Microsoft DAO 3.0 Object Library ) from the Tools | References selection dialog box.

DAO Supplied with the Professional Edition versus Standard Edition

The Standard edition of Visual Basic contains a 32-bit version of the Jet engine. With the Standard edition, you cannot directly manipulate the DAO—you can only use the built-in Data control and data bound controls in order to manipulate data stored in a Jet database.

Note

To create a database with the Standard edition of Visual Basic, you can use either the Data Manager application distributed with the Standard edition of Visual Basic or the 32-bit version of MS Access.

The Professional edition of Visual Basic contains two versions of the Jet engine:

• Version 2.5, a 16-bit version of the Jet Engine that provides all the new features of the DAO while maintaining backwards compatibility with applications created using Visual Basic 3.0.
  
  
• Version 3.0, a 32-bit version of the Jet engine that provides all the new features of the DAO.
  
  

Note

The 32-bit version of the DAO does not support any of the older methods contained in Visual Basic 3.0. For example, the older CreateDynaset method has been replaced in the 32-bit version with the CreateRecordset method.

Bound Controls versus the Jet Engine

In the preceding chapter, you saw how the Data control allows you to quickly create an application to view or edit existing records. However, you cannot use the Data control to add, delete, or change many of the underlying properties of the recordset. To make these changes, you must drop down to the DAO layer.

Data Access Object Model

The Data Access Object (DAO) model is a hierarchy of objects and collections (see Figure 28.1). In order to access the information displayed in Figure 28.1, search the Help file supplied with Visual Basic for the topic Jet Database Engine.

Figure 28.1. The Data Access Object model is a hierarchy.

The following sections discuss each of the objects or collections that make up the Data Access Object model.

DBEngine Object

The DBEngine object is the top layer of the DAO. All other objects and collections are contained in the DBEngine object. Although you do not have to define the DBEngine in your application, you cannot have more than one instance of this object in your application. The DBEngine object contains and defines the Workspace and Error objects and collections.

Note

At any one time, you can run a maximum of 10 applications that use the Jet engine.

DBEngine Object Properties

The following properties are available for use by the DBEngine object:

• DefaultUser/DefaultPassword. This property provides the default user information for all created workspaces. This information is used only when the user information is not supplied during the creation of a workspace. The property needs to be set only when working with secured MS Access databases. This property is provided for backward compatibility. Use the User and Password properties of the Workspace to maintain future compatibility.
  
  
• IniPath. The IniPath property is used to set or retrieve the path to the file containing the location of the ISAM.DLL files. The files are needed by Visual Basic to open non-Access-based databases. The IniPath property must be set before any event that initializes the database engine or a runtime error will occur.
  
  

Note

The behavior of the IniPath property varies between the 32-bit and 16-bit versions of Windows. In the 16-bit version, the setting of the ISAM DLL affects all applications currently running on the user's computer. In the 32-bit system, the setting affects only the current application.

• LoginTimeout. This property specifies the period of time that the Jet engine should wait for a response from a login request to an ODBC database.
  
  
• Version. The Version property is a read-only property that returns the version of the DBEngine currently in use.
  
  

DBEngine Object Methods

The following methods are available to the DBEngine:

• CompactDatabase. The CompactDatabase method defragments your database, allowing it to use space more efficiently and improving data access performance. It is possible to change the database version using the CompactDatabase method. However, this method cannot convert nontabular information stored in the database.
  
  
• CreateWorkspace. The CreateWorkspace method creates a workspace that can later be used to open databases. When you create a new workspace, that workspace is added automatically to the Workspace collection. The CreateWorkspace method uses the following three parameters:
  

Parameter

Description

• Once a workspace has been created, you cannot change any of its properties.
  
  The following example shows the creation of a workspace on an unsecured database:
  
  Dim wstest As Workspace
  Set wstest = DBEngine.CreateWorkspace(0)
  
  If the database is secured, you must include the following expression to create the workspace:
  
  wstest = DBEngine.CreateWorkspace("testworkspace", "User1", "password1")
  
  
• Idle. The Idle method provides the database engine with the necessary time to perform housekeeping tasks. These tasks were not previously possible during a period of heavy database activity. Use of the Idle method is important in a multiuser environment because it ensures that all writes have been made. Using the Idle method's optional dbFreeLocks parameter forces the database engine to release all the currently held read locks to be released, freeing previously held memory. Using the Idle method is similar to using the DoEvents statement in your application to allow other system events to occur.
  
  

NOTE

If your operations are part of a transaction, there is no need to invoke the Idle method because the equivalent action is provided by the CommitTrans method, which is discussed later.

• RegisterDatabase. The RegisterDatabase method records the ODBC connect information in the ODBC.INI file. The driver for the ODBC source reads this information and uses it to open a connection to the database. Because the database must be registered only once, it is perhaps a better idea to use the Windows Control Panel-based OBDC application to register your database. Alternatively, include the registration as part of you application setup.
  
  
• RepairDatabase. The RepairDatabase method attempts to correct errors in a corrupt MS Access database. If a database is damaged, you will receive a trappable error when you try to open the database.
  
  

NOTE

You should compact a database that has been repaired to ensure that the database is no longer fragmented.

Workspace Object/Collection

The Workspace object allows you to open databases so that you can perform operations on the database object. You can have multiple workspaces in an application.

The Workspace object contains and defines the User, Group, and Database objects and collections.

Note

Once you have created a workspace and added it to the Workspace collection, you cannot alter any of its properties.

Workspace Object Properties

The following properties are available to the Workspace object:

• Count. The Count property is used to indicate the number of workspaces contained in the Workspace collection. If the count equals 0, there are no workspaces contained in the Workspace collection.
  
  
• Name. The Name property is used to assign a meaningful name to the workspace.
  
  
• Username and Password. The UserName parameter defines the user creating or owning the Workspace. The Password parameter defines the user's password.
  
  The Username and Password properties are needed only when working with secured MS Access databases.
  
  

Workspace Object Methods

The following methods are available to the Workspace object:

• BeginTrans, CommitTrans, and RollBack. The BeginTrans, CommitTrans, and RollBack methods are all used as part of a transaction block. Reasons for using these methods are as follows:
  
  
• For speed. Because all writes are deferred until the CommiTrans method is executed, using the method speeds up write access considerably. To maintain the integrity of a database transaction. If part of a transaction cannot be completed, you can use the RollBack method to return the database(s) to the state they were in just before the BeginTrans statement.
  
  

In the following code example, the Notes field in the Titles table of the BIBLIO database is set to Test Notes when the Yes button is selected from the MsgBox that is displayed.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

If rstitles.RecordCount = 0 Then

    Exit Sub

End If

wstitle.BeginTrans

Do While rstitles.EOF = False

    rstitles.Edit

    rstitles("notes") = "Test Notes "

    rstitles.Update

    rstitles.MoveNext

Loop

If MsgBox("Do you wish to Save the Changes you have Made ", vbYesNo, "", "", _0) = vbYes Then

    wstitle.CommitTrans

Else

    wstitle.Rollback

End If

• Close. The Close method closes the specified workspace and removes it from the Workspace collection. If an attempt is made to close the default workspace, an error is generated.
  
  
• CreateDatabase. The CreateDatabase method is used to create a new MS Access database and assign it to a Database object. When creating the database, you can specify the following information:
  
  Language-specific information that relates to the collating method used by the database.
  
  The target version of MS Access that the database is compatible with.
  
  Whether the database is to be encrypted.
  
  Once the database has been created, it does not contain Data Access objects. These must be created by your application or by using a database-manipulation tool such as MS Access.
  
  The following example shows the creation of a new MS Access database using a general collating order. The database is created as a Jet 3.0 (32-bit) or Jet 2.5 (16-bit) database depending on the version of the DAO used in your project.
  
  Dim wstitle As Workspace
  Dim dbnew As Database
  
  Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")
  Set dbnew = wstitle.CreateDatabase("c:\vb40\new.mdb", dblangGeneral)
  
  
• CreateGroup. The CreateGroup method is used to create a new Group security object. The CreateGroup method has two parameters:
  

Parameter

Description

• CreateUser. The CreateUser method is used to create a new User security object. The CreateUser method has three parameters:
  

Parameter

Description

• OpenDatabase. The OpenDatabase method opens a physical database and assigns it to a Database type object. The OpenDatabase method uses the following parameters:
  

Parameter

Description

• The following code sample opens the BIBLIO.MDB database as a read-only database:
  
  Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, True)
  
  The following code sample opens the TITLE.DBF file created in the previous chapter (please note that the IniPath property of DBEngine must have been previously set):
  
  Set dbbiblio = wstitle.OpenDatabase("c:\vb40", False, TRUE,"DBASEIII;")
  Set rstitles = dbbiblio.OpenRecordset(" select * from title", dbOpenDynaset)
  
  The following sample code prompts you to select a valid ODBC data source when opening the database:
  
  Set dbbiblio = wstitle.OpenDatabase("", False, TRUE,"ODBC;")
  
  

Database Object/Collection

The Database object allows you to manipulate databases residing on your computer or on a network. The Database object contains and defines the TableDef, QueryDef, and Recordset objects and collections.

Database Object/Collection Properties

The following properties are available to the Database object/collection:

• CollatingOrder. The CollatingOrder property is read-only at runtime. The CollatingOrder corresponds to the sort order of the language-specific value that was used when the database was created. If you want to change this value, you must compact the database and specify a new CollatingOrder.
  
  
• Connect. The Connect property is read-only at runtime. The value of the Connect property corresponds to the Connect string used when then database was opened.
  
  
• Count. The Count property indicates the number of Database objects contained in the Database collection. If Count equals 0, there are no databases contained in the Database collection.
  
  
• Name. The database Name property is read-only at runtime. The value of the Name property corresponds to the name of the physical database used when the database was opened or assigned to a data control.
  
  
• QueryTimeout. The QueryTimeout property specifies the period of time the Database object should wait for a response from an ODBC database.
  
  
• RecordsAffected. The RecordsAffected property is read-only; it indicates the number of records that were changed, added, or deleted as a result of an action query performed on the Database object.
  
  The following example displays a message box that shows the number of record that would change if the Yes button in the message box is selected. Note that if Yes is selected, the Notes field in the Titles table contains Test Notes for all records.
  
  Dim wstitle As Workspace
  Dim dbbiblio As Database
  Dim rstitles As Recordset
  Dim csql As String
  
  Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")
  Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)
  Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)
  
  If dbbiblio.Transactions = True Then
  wstitle.BeginTrans
  End If
  
  csql = " update titles set notes = 'Test Notes' "
  dbbiblio.Execute csql, dbFailOnError
  
  If MsgBox("There have been " & dbbiblio.RecordsAffected & " Records changed Do you wish to Save the Changes you have Made ", vbYesNo, "", "", 0) = vbYes Then
  If dbbiblio.Transactions = True Then
  wstitle.CommitTrans
  End If
  Else
  If dbbiblio.Transactions = True Then
  wstitle.Rollback
  End If
  End If
  
  
• Transactions. The Transactions property is read-only at runtime. The value of the property can be either TRUE or FALSE, indicating whether the Database object supports transaction processing.
  
  

NOTE

You should always make sure that the Database object supports transactions before executing any transaction-related methods.

The following example checks whether or not transactions are allowed for a Database object. If they are, the actions performed on the Database object are part of a transaction block. Otherwise, the transaction block is ignored.

sub begintransaction (ws as workspace, db as database)

if db.transaction= true then

    ws.BeginTrans

endif

end sub

• Updatable. The Updatable property is read-only at runtime. The value of the property can be either TRUE or FALSE, indicating whether any changes can be made to the underlying Database object. The value of this property is determined by the choice options used in creating the Database object.
  
  

The following example checks the Updatable property to determine whether or not an edit action can be completed:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, True)

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

If dbbiblio.Updatable = True Then

    rstitles.Edit

Else

    MsgBox ("This Database Object Is Read Only ")

End If

• Version. The Version property is read-only at runtime. This property indicates the version of the DAO engine that was used to create the database. This property differs from the DBEngine property in that you can open any version of a Microsoft database with the DBEngine version 3.0.
  
  

The following example displays the version of the DBEngine and the version of the BIBLIO.MDB database. With the 16-bit version of Visual Basic, the version of the DBEngine should be 2.5 and the version of the BIBLIO.MDB database should be 2.0. With the 32-bit version of Visual Basic, the version of the DBEngine would be 3.0, and the version of the BIBLIO.MDB database would be unchanged.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, True)

MsgBox " The Version of the DBEngine is " _

& DBEngine.Version & ". The version of the " _

& dbbiblio.Name & " Database is " _

& dbbiblio.Version & "."

Database Object/Collection Methods

The following methods are available to the Database object/collection:

• OpenRecordset/CreateDynaset/CreateSnapshot/OpenTable. The OpenRecordset method is a replacement for the CreateDynaset, CreateSnapshot, and OpenTable methods provided in Visual Basic 3.0. The CreateDynaset, CreateSnapshot, and OpenTable methods are provided only for compatibility purposes. It is recommended that you use the OpenRecordset method because it provides additional functionality. The OpenRecordset method uses the following parameters:
  

Parameter

Description

The following examples use the OpenRecordset equivalents in place of the older-style CreateDynaset, CreateSnapshot, and OpenTable methods:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Dim dstitles As Dynaset

Dim sntitles As Snapshot

Dim tbltitles As Table

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, True)

' old method to create a Dynaset Object

Set dstitles = dbbiblio.CreateDynaset("Select * from titles")

'New Method to Open a Dynaset Type Recordset

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

' old method to create a Table Object

Set tbltitles = dbbiblio.OpenTable("titles")

'New Method to Open a Dynaset Type Recordset

Set rstitles = dbbiblio.OpenRecordset("titles", dbOpenTable)

' Old method to create a SnapShot Object

Set sntitles = dbbiblio.CreateSnapshot("Select * from titles")

'New Method to Open a Dynaset Type Recordset

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", _dbOpenSnapShot)

• An additional benefit to using the Recordset object is that when you are creating functions to retrieve data (see Chapter 27, "The Data Control"), you need only one function to handle all types of recordsets. On the other hand, if you work with the older methods, you need one function for each of the different recordset types (snapshot, Dynaset, and table).
  
  
• CreateProperty. The CreateProperty method allows you to add additional properties to the Database object. The additional properties then become members of the Property collection.
  
  The CreateProperty method has the following four parameters:
  

Parameter

Description

The following example adds a Boolean Dirty property to the Database object and sets its default value to TRUE:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim propdirty As Property

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Create the New Property

Set propdirty = dbbiblio.CreateProperty("dirty", dbBoolean, True, False)

' Save the Property definition

dbbiblio.Properties.Append propdirty

Debug.Print propdirty.Value

propdirty.Value = false

Debug.Print propdirty.Value

• CreateQueryDef. The CreateQueryDef method creates a new QueryDef that is stored in your database. Once the query has been created, it can be used to create recordsets from the database.
  
  The CreateQueryDef method has the following parameters:
  

Parameter

Description

The following sample code creates a new QueryDef and uses the newly created QueryDef to create a recordset:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim qrynew As QueryDef

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Create a New QueryDef

Set qrynew = dbbiblio.CreateQueryDef("new", "Select * from titles where pubid _= 13")

' Load The Recordset based on the New Query

Set rstitles = dbbiblio.OpenRecordset("new", dbOpenDynaset)

• CreateRelation. The CreateRelation method creates a Relation object. The Relation object enables the Jet engine to provide referential integrity between two TableDefs or two QueryDefs. The Relation object and collection are covered in more depth later in this chapter.
  
  The CreateRelation method has the following parameters:
  

Parameter

Description

The following sample code creates a new Relation object between the Publishers table and the Titles table in the BIBLIO.MDB database. The relationship is based on the PUDID field present in both tables. This relationship allows multiple titles per publisher and supports cascaded updates and deletions.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim relnew As Relation

Dim fld1 As Field

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Create a New Relation

Set relnew = dbbiblio.CreateRelation("new", "publishers", "titles", _

dbRelationLeft + dbRelationUpdateCascade + dbRelationDeleteCascade)

' Set the parent table field for the Relation

Set fld1 = relnew.CreateField("pubid")

' Set the Child Table field for the Relation

fld1.ForeignName = "pubid"

' Update the Fields portion of the Relations Collection

relnew.Fields.Append fld1

' Update the New Relation to the Relations Collection

dbbiblio.Relations.Append relnew

• CreateTableDef. The CreateTableDef method creates a new table in the Database object. If you try to create a table with the name of an existing table, a trappable error occurs.
  
  The CreateTableDef method has the following parameters:
  

Parameter

Description

The following sample code adds the table Books to the BIBLIO.MDB database. The table contains two fields: Title and Author.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim newtable As TableDef

Dim fld1 As Field

Dim fld2 As Field

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' creates a table if Data Base specified in Parameter 1

Set newtable = dbbiblio.CreateTableDef("books")

' add the fields to the tabledef Object

Set fld1 = newtable.CreateField("")

fld1.Name = "Title"

fld1.Type = dbText

fld1.Size = 30

newtable.Fields.Append fld1

Set fld2 = newtable.CreateField("")

fld2.Name = "Author"

fld2.Type = dbText

fld2.Size = 30

newtable.Fields.Append fld2

' Now add the table definition to the database object

' ——————————————————————

dbbiblio.TableDefs.Append newtable

• Execute. The Execute method allows you to run an action QueryDef or action SQL statements on the Database object. The Execute method is used to run non-record-returning action queries.
  
  

NOTE

To enhance your application's performance, nest Execute statements between BeginTrans and CommiTrans statements.

• The Execute method has the following parameters:
  

Parameter

Description

The following sample code resets the Notes field in the Titles table to the comment This is a Test. If you do not want to make the changes permanent, select the No option from the message box and all the changes are rolled back.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Dim csql As String

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

wstitle.BeginTrans

csql = " update titles set notes = 'Test Notes' "

dbbiblio.Execute csql, dbFailOnError

'the following statement must be entered on one line

If MsgBox("There have been " & dbbiblio.RecordsAffected & "Records changed_

 Do you wish to Save the Changes you have Made ", vbYesNo, "", "", 0) = vbYes Then

    wstitle.CommitTrans

Else

    wstitle.Rollback

End If

• ExecuteSql. The ExecuteSql method is used only against ODBC-type databases. This method is provided for compatibility with earlier versions of Visual Basic. The only difference between the Execute method and the ExecuteSql method is that the ExecuteSql method returns the number of rows affected. As with the Execute method, the SQL to be executed must be a non-record-returning SQL statement.
  
  The Execute method has only one parameter: SQLstatement, an SQL statement that does not return any records.
  
  After successful completion of ExecuteSql, the number of rows affected is returned, as shown in the following example. If this code was run against an ODBC-type database, the number of records that were changed would be contained in the nrows field.
  
  

dim nrows as long

csql = " update titles set notes = 'Test Notes' "

nrows = dbbiblio.ExecuteSQL(csql)

TableDef Object/Collection

The TableDef object allows you to manipulate the structure of the tables in the database. The TableDef object contains and defines the Field and Index objects and collections.

TableDef Object/Collection Properties

The following properties are available to the TableDef object/collection:

• Attributes. This property is read-only at runtime. The TableDef's attributes are determined when the table is created by using the CreateTableDef method of the Database object.
  
  
• Connect. This property is read-only at runtime. The TableDef's Connect property is determined when the table is created by using the CreateTableDef method of the Database object.
  
  
• Count. The Count property indicates the number of TableDef objects contained in the TableDef's collection. If Count equals 0, there are no TableDefs contained in the TableDef collection.
  
  
• DateCreated and LastUpdated. The DateCreated and LastUpdated properties are both read-only at runtime. The DateCreated property contains the date and time of the table's creation; the LastUpdated property contains the date and time of the last modification.
  
  
• Name. The Name property is read-only at runtime. The Name property contains the name of the table as it was defined by the CreateTableDef method of the Database object.
  
  
• RecordCount. The RecordCount property is a read-only property that contains the total number of records in the table defined by the TableDef object.
  
  
• SourceTableName. The SourceTableName property is read-only at runtime for all tables in the TableDef collection. This property is Read/Write for an external table (at least until the table is added to the TableDef collection).
  
  
• Updatable. The Updatable property indicates whether or not changes can be made to the TableDef object. This property returns TRUE if changes can be made. For attached tables, this value is always FALSE.
  
  
• ValidateRule. The ValidateRule property indicates the conditions that must be met before a new or changed record can be successfully saved to the table. Using this property, you can allow the Jet engine to perform validation of your data.
  
  
• ValidationText. The ValidationText property specifies the text to be displayed when the results of ValidateRule return FALSE.
  
  

TableDef Object/Collection Methods

The following methods are available to the TableDef object/collection:

• Append. The Append method adds a new TableDef object to the TableDef collection.
  
  
• CreateField. The CreateField method defines the characteristics of a field that will become part of the TableDef object.
  
  The CreateField method has the following parameters:
  

Parameter

Description

The following sample code creates a field named Month in the Titles table:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim tbltitles As TableDef

Dim fld1 As Field

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Get the Existing Table Definition

Set tbltitles = dbbiblio!Titles

' Create Field object.

Set fld1 = tbltitles.CreateField("month", dbText, 12)

    ' Append MyField to Fields collection.

tbltitles.Fields.Append fld1

Note

Notice the use of the ! character when setting the TableDef. It is used because we are accessing a member of a collection.

CreateIndex. The CreateIndex method is used to create a new Index object. The CreateIndex method has only one parameter: Name, which defines the name used to refer to the index.

The following sample code creates an index on the newly created Month field in the Titles table:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim indx1 As Index

Dim fld1 As Field

Dim tbltitles As TableDef

' Create the Workspace

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

' Open the Database

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Open the Table Definition

Set tbltitles = dbbiblio!Titles

' Create the new Index object.

Set indx1 = tbltitles.CreateIndex("nonthindx")

' define the index field

Set fld1 = indx1.CreateField("month")

' Define the index as non primary, non unique, and non required

indx1.Unique = False

indx1.Primary = False

indx1.Required = False

indx1.Fields.Append fld1 ' Save Index definition by appending it to Indexes _collection.

tbltitles.Indexes.Append indx1

• CreateProperty. The CreateProperty method allows you to add additional properties to the TableDef object. The additional properties become members of the Property collection.
  
  The CreateProperty method has the following parameters:
  

Parameter

Description

The following sample code adds a Contents property using the CreateProperty method to the Title TableDef object and sets its default value to Test:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim proptext As Property

Dim tbltitle As TableDef

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

Set tbltitle = dbbiblio!titles

' Create the New Property

Set proptext = tbltitle.CreateProperty("contents", dbText, "Test", False)

' Save the Property definition

tbltitle.Properties.Append proptext

Debug.Print proptext.Value

proptext.Value = " testing property"

Debug.Print proptext.Value

• Delete. The Delete method removes a TableDef object from the TableDef collection.
  
  
• OpenRecordset. The OpenRecordset method opens a Recordset object from a TableDef object. The difference between the OpenRecordset method for a TableDef and the OpenRecordset method for a Database is that when opening a recordset from a TableDef, you cannot specify the Source parameter. Instead, all records from the TableDef are returned in the recordset.
  
  The OpenRecordset method has the following parameters:
  

Parameter

Description

The following sample code creates a recordset from the TableDef of the Titles table:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim proptext As Property

Dim tbltitle As TableDef

Dim rstitle As Recordset

' open the Workspace

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

' open the Database

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Set the Table Definition

Set tbltitle = dbbiblio!titles

' Open the RecordSet

Set rstitle = tbltitle.OpenRecordset(dbOpenDynaset)

• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  
• RefreshLink. The RefreshLink method ensures that the information contained in attached tables is complete and up to date.
  
  

NOTE

It is good practice to perform a Refresh after you have performed a RefreshLink to ensure that all the collections have been updated.

Field Object/Collection

The Field object allows you to directly manipulate the data stored in the database. It is through the Field object that data is read and placed in fields.

Field Object/Collection Properties

The following properties are available to the Field object/collection:

• AllowZeroLength. The value of the AllowZeroLength property is read/write until the field you are creating has been added to the Fields collection. Once the field is added to the collection, the AllowZeroLength property becomes read-only. The AllowZeroLength property determines whether the value of the field can be an empty string. If the value of this property is TRUE, any attempt to place a null value in this field results in a runtime error.
  
  
• Attributes. The value of the Attributes property is read/write until the field you are creating has been added to the Fields collection. Once the field is added to the collection, the Attributes property becomes read-only. The Attributes property allows you to define additional information about the field's characteristics. The value of the Attributes property can be a made up of a combination of the following constants:
  

Constant

Description

• CollatingOrder. The CollatingOrder property is a read-only property that indicates the method in which the field is to be sorted. See the CollatingOrder property of the Database object, earlier in this chapter, for more information.
  
  
• Count. The Count property indicates the number of Field objects contained in the Field collection. If Count equals 0, there are no fields contained in the Field collection.
  
  
• DataUpdateable. The DataUpdateable property is a read-only property that indicates whether the contents of the field can be changed.
  
  
• DefaultValue. The DefaultValue property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the value of the DefaultValue property is read-only. DefaultValue contains the value automatically assigned to a field when a new record in a table containing that field is added.
  
  
• ForeignName. The ForeignName property refers to the name of the field in a table.
  
  
• Name. The Name property is read/write at runtime until the field you are creating has been added to the Field collection. The Name property identifies the field so that the field can be accessed by your application.
  
  
• OrdinalPosition. The OrdinalPosition property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the OrdinalPosition property becomes read-only. OrdinalPosition represents where in the Field collection the field you are creating is to appear.
  
  
• Required. The Required property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the Required property becomes read-only. The Required property indicates that the value of the field must not contain a #NULL# value. If no value is supplied, a trappable runtime error is generated.
  
  
• Size. The Size property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the Size property becomes read-only. The Size property indicates the maximum size of data that can be stored in the field. The maximum size of a field is limited by the type of data the field can store (for example, a dbText field can contain a maximum of 255 bytes of data). If your application makes an attempt to place more data than is allowed by the field, a trappable runtime error is generated.
  
  
• SourceField. The SourceField property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the SourceField property becomes read-only. SourceField indicates the original name of a field in an attached table.
  
  
• SourceTable. The SourceTable property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the SourceTable property becomes read-only. The SourceTable property indicates the name of the attached table containing the field.
  
  
• Type. The Type property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the Type property becomes read-only. The Type property indicates the type of field that has been defined. You can use the Type property to determine what the contents of the field should be.
  
  The following constants are available for use with the Type property. The size of the field is automatically determined by the type of the field you are creating.
  

Constant

Description

• ValidateOnset. The ValidateOnset property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the ValidateOnset property becomes read-only. The ValidateOnset property determines at what point the field-level validation is carried out. If the value of the property is TRUE, the validation defined by the validation rule of the field takes places when the value of the field is modified. If the value of the property is FALSE, the validation occurs as part of the update method of the recordset.
  
  Note: The ValidateOnset property and all other validate properties are available only when using the new type of recordset objects.
  
  
• ValidateRule. The ValidateRule property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the ValidateRule property becomes read-only. The ValidateRule property defines the validation that the Jet engine is to perform on your data. The timing of the validation is defined by the setting of the ValidateOnset property.
  
  
• ValidationText. The ValidationText property is read/write at runtime until the field you are creating has been added to the Field collection. Once the field is added to the collection, the ValidationText property becomes read-only. The ValidationText property defines the message displayed to the user if the contents of the field do not pass the ValidateRule.
  
  
• Value. The Value property is read/write at runtime. The Value property retrieves or sets the value of a field at runtime.
  
  Note: When retrieving data from a Field object, ensure that you do not try to assign a #NULL# value to a Visual Basic control. Doing so causes a runtime error.
  
  

The following example shows how you can view the properties of all of the fields contained in the BIBLIO.MDB database supplied with Visual Basic:

1. Open a new project.
   
   
2. Add a form to the project.
   
   
3. Place the following controls on the form:
   

Control

Name

Caption

Top

Left

Width

Height

1. Add a frame to the form and place these additional controls on the Frame control:
   

Control

Name

Caption

Top

Left

Width

Height

1. Add the following code to the Form_load event:
   
   Dim wstitle As Workspace
   Dim dbbiblio As DATABASE
   Dim X As Integer
   
   Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")
   Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)
   
   For X = 1 To dbbiblio.TableDefs.Count - 1
   If dbbiblio.TableDefs(X).Attributes <> dbSystemObject - 2 And dbbiblio.TableDefs(X).Attributes <> dbReadOnly Then
   comtable.AddItem dbbiblio.TableDefs(X).Name
   End If
   
   Next
   
   
2. Add the following code to the comtable_click event:
   
   Dim X As Integer
   
   comfield.Clear
   
   Set rstitles = dbbiblio.OpenRecordset(comtable.Text, dbOpenTable)
   
   For X = 1 To rstitles.Fields.Count - 1
   comfield.AddItem rstitles.Fields(X).Name
   Next
   
   
3. Add the following code to the cmdproperties_click event:
   
   Dim rstitles As Recordset
   
   If Len(comfield.Text) = 0 Or Len(comtable.Text) = 0 Then
   Exit Sub
   End If
   
   Frame1.Visible = False
   
   Set rstitles = dbbiblio.OpenRecordset(comtable.Text, dbOpenTable)
   If rstitles.RecordCount = 0 Then
   Exit Sub
   End If
   
   rstitles.MoveFirst
   
   If Not IsNull(rstitles(comfield.Text)) Then
   txtnotes.Text = rstitles(comfield.Text).Value
   End If
   
   txttype.Text = rstitles(comfield.Text).Type
   txtsourcetable.Text = rstitles(comfield.Text).SourceTable
   txtsourcefield.Text = rstitles(comfield.Text).SourceField
   txtsize.Text = rstitles(comfield.Text).Size
   txtattributes.Text = rstitles(comfield.Text).Attributes
   txtcolattingorder.Text = rstitles(comfield.Text).CollatingOrder
   txtdefaultvalue.Text = rstitles(comfield.Text).DefaultValue
   txtvalidaterule.Text = rstitles(comfield.Text).ValidationRule
   txtvalidationtext.Text = rstitles(comfield.Text).ValidationText
   
   If rstitles(comfield.Text).Required = True Then
   chkrequired.Value = vbChecked
   Else
   chkrequired.Value = vbUnchecked
   End If
   
   If rstitles(comfield.Text).DataUpdatable = True Then
   chkdataupdateable.Value = vbChecked
   Else
   chkdataupdateable.Value = vbUnchecked
   End If
   
   If rstitles(comfield.Text).AllowZeroLength = True Then
   chkAllowZeroLength.Value = vbChecked
   Else
   chkAllowZeroLength.Value = vbUnchecked
   End If
   
   If rstitles(comfield.Text).ValidateOnSet = True Then
   chkvalidate.Value = vbChecked
   Else
   chkvalidate.Value = vbUnchecked
   End If
   
   txtordinalposition.Text = rstitles(comfield.Text).OrdinalPosition
   txtname.Text = rstitles(comfield.Text).Name
   
   
4. Add the following code to the cmdend_click event:
   
   If index = 1 Then
   Frame1.Visible = True
   Exit Sub
   End If
   
   Unload Me
   Set form228 = Nothing
   
   
5. Now run the project. Select a table from the Tables combo box and then select a field from the Fields combo box (see Figure 28.2). When you click the Properties button, you see all the properties for the selected field (see Figure 28.3).
   
   

Figure 28.2. Field selection.

Figure 28.3. Properties of the selected field.

Field Object/Collection Methods

The following methods are available to the Field object/collection:

• Append. The Append method is used to add a new Field object to the Field collection.
  
  
• AppendChunk. The AppendChunk method is used to assign a string field exceeding 255 bytes.
  
  
• CreateProperty. The CreateProperty method allows you to add additional properties to the Field object. The additional properties become members of the Property collection.
  
  The CreateProperty method has the following parameters:
  

Parameter

Description

• Delete. The Delete method is used to remove a field from the Field collection.
  
  
• FieldSize. The FieldSize method returns the size of a large field (greater than 255 bytes). Use this method in conjunction with the AppendChunk and GetChunk methods.
  
  
• GetChunk. The GetChunk method retrieves the contents of a large field (greater than 255 bytes) and assigns it to a string variable.
  
  
• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  

Index Object/Collection

The Index object contains all the information relating to indexes that exist for tables in the database. The Index object contains and defines the Field objects and collections.

Index Object/Collection Properties

The following properties apply to the Index object/collection:

• Count. The Count property indicates the number of Index objects contained in the index collection. If Count equals 0, there are no indexes contained in the Index collection.
  
  
• Foreign. The Foreign property indicates whether the Index has been created on a attached table. This property is read-only at runtime.
  
  
• IgnoreNulls. The IgnoreNulls property is read/write at runtime until the index has been added to the index collection. Once the index is added to the collection, the IgnoreNulls property becomes read-only. The IgnoreNulls property indicates whether any of the values contained in the key fields of the index can contain a #NULL# value. If the IgnoreNulls property is set to FALSE, and the Required property is set to TRUE, a runtime error is generated when any of the fields making up the index is #NULL#.
  
  
• Name. The Name property of an index is read/write at runtime until the index has been added to the Index collection. Once the index is added to the collection, the Name property becomes read-only. The Name property is used to access indexes in your application.
  
  
• Primary. The Primary property of an index is read/write at runtime until the index has been added to the Index collection. Once the index is added to the collection, the Primary property becomes read-only. The Primary property indicates that this index is the main index for the table. The primary index must have a unique value for all records. You cannot assign #NULL# values to a primary index.
  
  
• Required. The Required property of an index is read/write at runtime until the index has been added to the Index collection. Once the index is added to the collection, the Required property becomes read-only. The Required property indicates that the Value property for each of the fields making up the index expression of your table cannot be a #NULL# value.
  
  
• Unique. The Unique property of an Index is read/write at runtime until the index has been added to the Index collection. Once the index is added to the collection, the Unique property becomes read-only. The Unique property indicates that the values contained in the fields making up the index must be unique for each record in the table. If the values are not unique, a runtime error is generated. The Unique property is automatically set to TRUE when an Index is defined as the primary index.
  
  

Index Object/Collection Methods

The following methods apply to the Index object/collection:

• Append. The Append method is used to add a new Index object to the Index collection.
  
  
• CreateField. The CreateField method is used to define the index expression to be used on the table.
  
  

In the following sample code, an Index object is created for the Titles table using the Month field:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim indx1 As Index

Dim fld1 As Field

Dim tbltitles As TableDef

' Create the Workspace

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

' Open the Database

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Open the Table Definition

Set tbltitles = dbbiblio!Titles

' Create the new Index object.

Set indx1 = tbltitles.CreateIndex("nonthindx")

' define the index field

Set fld1 = indx1.CreateField("month")

' Define the index as non primary, non unique, and non required

indx1.Unique = False

indx1.Primary = False

indx1.Required = False

indx1.Fields.Append fld1 ' Save Index definition by appending it to Indexes collection.

tbltitles.Indexes.Append indx1

• CreateProperty. The CreateProperty method allows you to add additional properties to the Index object. The additional properties become members of the Property collection.
  
  The CreateProperty method has the following parameters:
  

Parameter

Description

• Delete. The Delete method removes an Index object from the Index collection.
  
  
• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  

QueryDef Object/Collection

The QueryDef object allows you to manipulate the queries stored in your database. The QueryDef object contains and defines the Field and Parameter objects and collections.

QueryDef Object/Collection Properties

The following properties are available to the QueryDef object/collection:

• Connect. The Connect property is needed only when you are accessing a non-Microsoft Access database. The value of the Connect property depends on the type of database to which you are connecting. If you are connecting to an ODBC database, specify ODBC in the Connect property and also supply any additional information such as USERID and PASSWORD. The additional information depends on the type of ODBC database. Further information about the Connect string for ODBC databases is supplied with the ODBC database driver.
  
  
• Count. The Count property indicates the number of QueryDef objects contained in the QueryDef collection. If Count equals 0, there are no QueryDefs contained in the QueryDef collection.
  
  
• DateCreated and LastUpdated. The DateCreated and LastUpdated properties are both read-only at runtime. The DateCreated property contains the date and time of the QueryDef's creation; the LastUpdated property contains the date and time of the last modification.
  
  
• LogMessages. The LogMessages property indicates whether the messages received during execution of a QueryDef against an ODBC database are to be recorded. The LogMessages property must be added to the QueryDef object using the CreateProperty method, as shown in the following example:
  
  Dim wstitle As Workspace
  Dim dbbiblio As Database
  Dim qrynew As QueryDef
  Dim rstitles As Recordset
  Dim propmessages As Property
  
  Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")
  
  Set dbbiblio = wstitle.OpenDatabase("", False, False,"ODBC;")
  ' Create a New QueryDef
  Set qrynew = dbbiblio.CreateQueryDef("new", "Select * from titles where pubid = 13")
  ' Load The Recordset based on the New Query
  Set rstitles = dbbiblio.OpenRecordset("new", dbOpenDynaset)
  
  Set propmessages = qrynew.CreateProperty("logmessages", dbBoolean, True, False)
  ' Save the Property definition
  
  qrynew.Properties.Append propdirty
  ' Set the Query to Log messages
  qrynew.Logmessages= true
  
  
• Name. The Name property of a QueryDef is read/write at runtime until the QueryDef has been added to the QueryDef collection. Once the QueryDef is added to the collection, the Name property becomes read-only. The Name property provides the primary identification method for the QueryDefs in your application.
  
  
• ODBCTimeout. The ODBCTimeout property specifies the length of time the Jet engine will wait for a reply from an ODBC database before returning a time-out error to your application.
  
  
• RecordsAffected. The RecordsAffected property is a read-only property that indicates the number of records changed, added, or deleted as a result of an action query performed by the QueryDef object.
  
  
• ReturnsRecords. The ReturnsRecords property indicates whether a SQLPassThrough query is expected to return records to the application or whether it is an action query.
  
  
• SQL. The SQL property sets or reads the SQL contained in the QueryDef object.
  
  The following sample code uses the SQL property to change the expression used to create the recordset:
  
  Set qrynew = dbbiblio.CreateQueryDef("new", "Select * from titles where pubid _= 13")
  qrynew.SQL = " Select from titles where pubid=1"
  ' Load The Recordset based on the New Query
  Set rstitles = dbbiblio.OpenRecordset("new", dbOpenDynaset)
  
  
• Type. The Type property indicates the type of QueryDef you want to open or create. The following constants are available for use as the Type parameter (you can combine Type constants for the QueryDef by adding them together, using the + sign between constants):
  

Constant

Description

• Updateable. The Updateable property indicates whether any changes can be made to the QueryDef object.
  
  

QueryDef Object/Collection Methods

The following methods are available to the QueryDef object/collection:

• Append. The Append method is used to add a new QueryDef object to the QueryDef collection.
  
  
• Close. The Close method closes the QueryDef and reclaims any memory it may have been using.
  
  
• CreateProperty. The CreateProperty method allows you to add additional properties to the QueryDef object. The additional properties become members of the Property collection.
  
  The CreateProperty method has the following parameters:
  

Parameter

Description

• Delete. The Delete method removes a QueryDef object from the QueryDef collection.
  
  
• Execute. The Execute method allows you to run an action QueryDef or action SQL statements on the Database object. The Execute method is used to run non-record-returning action queries.
  
  Note: For best performance, nest the Execute method between BeginTrans and CommiTrans statements.
  
  The Execute method has only one parameter: Options. The Options parameter is used to further define the characteristics of the SQL or QueryDef you are running. The following constants are available for use with the Options parameter:
  

Constant

Description

The following sample code resets the Notes field in the Titles table to the comment This is a Test. If you do not want to make the changes permanent, select the No option from the message box; all the changes will be rolled back.

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim Qrynew as QUERYDEF

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

Set qrynew = dbbiblio.OpenQueryDef("new")

qrynew.sql= "update titles set notes = 'Test Notes'"

wstitle.BeginTrans

qrynew.Execute dbfailonError

If MsgBox("There have been " & qrynew.RecordsAffected & " Records changed Do you wish to Save the Changes you have Made ", vbYesNo, "", "", 0) = vbYes Then

    wstitle.CommitTrans

Else

    wstitle.Rollback

End If

• OpenRecordset. The OpenRecordset method creates a Recordset object from a QueryDef object. The difference between the OpenRecordset method for a QueryDef and that available for a Database object is that when opening a recordset from a QueryDef, you cannot specify the Source parameter.
  
  The OpenRecordset method of the QueryDef object has the following parameters:
  

Parameter

Description

The following sample code creates a recordset based on a QueryDef:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim proptext As Property

Dim tbltitle As TableDef

Dim qrynew As QueryDef

Dim rstitle As Recordset

' open the Workspace

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

' open the Database

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Set the QueryDef  Definition

Set qrynew = dbbiblio.OpenQueryDef("new")

qrynew.SQL = " Select * from titles "

' Open the RecordSet

Set rstitle = qrynew.OpenRecordset(dbOpenDynaset)

• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  

Parameter Object/Collection

The Parameter object allows you to specify parameters to be used by the QueryDef object.

Parameter Object/Collection Properties

The following properties are available to the Property object/collection:

• Count. The Count property indicates the number of Parameter objects contained in the Parameter collection. If Count equals 0, there are no parameters contained in the Parameter collection.
  
  
• Name. The Name property of a parameter is read/write at runtime until the parameter has been added to the Parameter collection. Once the parameter is added to the collection, the Name property becomes read-only. The Name property is the primary method of identification for Parameter object in your application.
  
  
• Type. The Type property is read/write at runtime until the parameter has been added to the Parameter collection. Once the parameter is been added to the collection, the Type property becomes read-only. The Type property indicates the type of parameter being defined. Use the Type property to determine what the contents of the parameter should be.
  
  The following constants are available for use with the Type parameter. The size of the parameter is automatically determined by the type of field you are creating.
  

Constant

Description

• Value. The Value property is read/write at runtime. The Value property is used to retrieve or set the value of a Parameter object at runtime.
  
  

Parameter Object/Collection Methods

The following method is available to the Parameter object/collection:

• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  

Recordset Object/Collection

The Recordset collection allows you to manipulate the records contained in the tables of the Database object. The Recordset object contains and defines the Field object and collection.

Recordset Object/Collection Properties

The following properties are available to the Recordset object/collection.

Note

The properties available to Recordset objects vary based on the type of recordset created. If a property is not supported by a specific type of recordset, this is noted at the end of the description of the property.

• AbsolutePosition. The AbsolutePosition property represents the current record's count from the beginning of the recordset. If there are no records present in the recordset, the value of AbsolutePosition is —1. It is possible (but not recommended) to position the recordset at a specific record by assigning the record number to this property. If the record does not exist, a trappable error occurs. If the current record is deleted, the AbsolutePosition property contains an invalid record number.
  
  Note: The first record in a recordset is 0.
  
  This property is not available for table-type recordsets.
  
  

The following example shows the use of the AbsolutePosition property to move through a recordset:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim rstitles As Recordset

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, True)

Set rstitles = dbbiblio.OpenRecordset(" select * from titles", dbOpenDynaset)

rstitles.AbsolutePosition = 1

• BOF. The BOF property is read-only at runtime. This property is used to determine whether the recordset is in the beginning-of-file (BOF) position. When the BOF property is TRUE, any attempt to move backwards in the recordset results in a runtime error.
  
  

The following example generates an error at runtime:

if dstitles.BOF= true then

    dstitles.moveprevious

endif

• Bookmark. The Bookmark property contains a unique value for each record in the recordset. You can use the Bookmark property to mark your current position in a recordset and later use the saved bookmark to return to the original record. The following example shows the use of Bookmark property in order to save the current position in the recordset and return to that position after a move operation.
  
  

dim sbookmark as string

' save the Bookmark

sbookmark= dtitles.bookmark

' change the record pointer

dstitles.movenext

' Restore the Record pointer to the previously saved bookmark.

dstitles.bookmark= sbookmark

• BookMarkable. The BookMarkable property indicates whether the current recordset supports bookmarks. Although all Microsoft Access databases support bookmarks, not all installable ISAMs support bookmarks. If you work with non-Microsoft-Access databases, verify that the recordset supports bookmarks before trying to read or restore the Bookmark property.
  
  
• CacheSize. The CacheSize property defines the number of records in the recordset to be stored in a local cache. The CacheSize property applies only to an ODBC-type data source.
  
  This property is not available for table-type or snapshot-type recordsets.
  
  
• CacheStart. The CacheStart property defines the first cacheable record in the recordset. The CacheStart property applies only to an ODBC-type data source.
  
  This property is not available for table-type or snapshot-type recordsets.
  
  
• Count. The Count property indicates the number of Recordset objects contained in the Recordset collection. If Count equals 0, there are no recordsets contained in the Recordset collection.
  
  
• DateCreated, LastUpdated, and LastModified. The DateCreated, LastUpdated, and LastModified properties are read-only at runtime. The DateCreated property contains the date and time of the Recordset's creation; the LastUpdated property contains the date and time of the last modification; the LastModified property contains the bookmark to the record that was last most recently added or changed.
  
  All three properties are not available for snapshot-type recordsets; the DateCreated and LastModified properties are not available on Dynaset-type recordsets.
  
  
• EditMode. The EditMode property is read-only at runtime. It can be used to determine the state of the current record. The following constants can be used in order to de-termine the state of the EditMode property.
  

Constant

Description

• EOF. The EOF property is read-only at runtime. This property is used to determine whether the recordset is in the end-of-file (EOF) position. When this property is TRUE, any attempt to move forward through the recordset results in a runtime error.
  
  

The following sample code generates an error at runtime:

if dstitles.EOF= true then

    dstitles.movenext

endif

• Filter. The Filter property further specifies which records are to be contained in the recordset. Using the Filter property is similar to using the WHERE clause in an SQL statement.
  
  This property is not available for table-type recordsets.
  
  
• Index. The Index property sets the active index of a recordset. The chosen index determines the order in which the records appear in the recordset. The recordset must have an active index before you can use the Seek method.
  
  This property is not available for snapshot-type or Dynaset-type recordsets.
  
  
• LockEdits The LockEdits property specifies which locking mechanism is used by the Jet engine. You can use one of two types of locking:
  
  If LockEdits is set to TRUE, a 2K page of records containing the current record is locked between the execution of the Edit or AddNew method and the Update method. This approach is sometimes called pessimistic locking.
  
  If LockEdits is set to FALSE, a 2K page of records containing the current edit record is locked only when the Update method is used. This approach is some times called optimistic locking.
  
  This property is not available on snapshot-type recordsets.
  
  
• Name. The Name property is read/write at runtime until the recordset has been added to the Recordset collection. Once the recordset is added to the collection, the Name property becomes read-only. The Name property provides the primary identification for the recordset in your application.
  
  
• NoMatch. The NoMatch property is read-only at runtime. The NoMatch property indicates whether the result of a search using either the Find or Seek method was successful.
  
  

Following is an example of the use of the NoMatch property:

' save the Bookmark

dstitles.findfirst "pubid =13"

if dstitles.nomatch= true then

    ' record was found

else

    ' record was not found

endif

• PercentPosition. The PercentPosition property represents the approximate record position of the record as a percentage of the total records in the table. If no records are present in the recordset, the value of this property is —1. You can position a recordset to a specific record by assigning a percentage value to the PercentPosition property. If the record does not exist, a trappable error occurs. If the current record is deleted, the PercentPosition property contains an invalid record pointer.
  
  Note: The first record in a recordset is 0.
  
  
• RecordCount. The RecordCount property is read-only at runtime. The property represents the exact number of records in a table-type recordset. For a Dynaset-type or snapshot-type recordset, the RecordCount property indicates only the highest record number that has been visited. If no records are in the recordset, the value of this property is 0.
  
  
• Restartable. The Restartable property is read-only at runtime. You can use this property to determine whether the recordset supports the ReQuery method.
  
  This property is not available for table-type recordsets.
  
  
• Sort. The Sort property is used to reorder the records in the recordset. Using the Sort property is similar to using the ORDER BY clause in an SQL statement.
  
  This property is not available for table-type recordsets.
  
  
• Transactions. The Transaction property is read-only at runtime. It is used to determine whether the recordset can support use of the BeginTrans, CommitTrans, and RollBack methods.
  
  This property is not available for snapshot-type recordsets.
  
  
• Type. The Type property is read-only at runtime. It is used to determine the type of recordset currently being used. The following constants are returned by the Type property in order to determine the type of the current recordset.
  

Constant

Description

• Updateable. The Updateable property indicates whether any changes can be made to the recordset. This property always returns FALSE on snapshot-type recordsets.
  
  
• ValidateRule. The ValidateRule property is read/write at runtime until the recordset has been added to the Recordset collection. Once the recordset is added to the collection, the ValidateRule property becomes read-only. The ValidateRule property indicates the type of record validation to be performed on the recordset. You can use this property to allow the Jet engine to perform the validation of your data.
  
  
• ValidationText. The ValidationText property is read/write at runtime until the recordset has been added to the Recordset collection. Once the recordset is added to the collection, the ValidationText property becomes read-only. The ValidationText property specifies the text that displays when the conditions set in the ValidateRule property are not met.
  
  

Recordset Object/Collection Methods

The following methods are available to the Recordset object/collection:

• AddNew. The AddNew method prepares an empty record that is added to the end of the recordset when the Update method is used. If the recordset is repositioned before the Update method is used, the added record is lost. Record locking defaults to pessimistic unless you set the LockEdits property to FALSE.
  
  This method is not available for snapshot-type recordsets.
  
  

The following example adds a new record to the Titles table. The Refresh method repositions the new record in the recordset.

' set optimistic locking

rstitles.lockedit= false

' add a new record

rstitles.addnew

rstitles("pubid")= 6

rstitles.("title") = "New Book "

' Update the new record

addnew.update

' refresh the recordset in order to reposition the new record

rstitles.refresh

• CancelUpdate. The CancelUpdate method cancels any changes pending to the recordset caused by the Edit or AddNew method.
  
  This method is not available for snapshot-type recordsets.
  
  

The following example cancels an AddNew method so that no changes are made to the recordset:

' set optimistic locking

rstitles.lockedit= false

' add a new record

rstitles.addnew

rstitles("pubid")= 6

rstitles.("title") = "New Book "

' Cancel the Record addition

addnew.Cancelupdate

• Clone. The Clone method enables you to create a copy of an existing recordset. The Clone method is useful when you need multiple copies of the same recordset to maintain different record positions.
  
  This method is not available for snapshot-type recordsets.
  
  

The following example clones the rstitles recordset to create a new recordset based on the contents of the first:

dim rstitleclone as recordset

set dstitlesclone= rstitles.clone()

• Close. The Close method closes the recordset and reclaims any memory it may have been using.
  
  
• CopyQueryDef. The CopyQueryDef method creates a duplicate of the QueryDef used to create the recordset. If the recordset was not created using a QueryDef, a #NULL# value is returned.
  
  This method is not available for table-type recordsets.
  
  
• Delete. The Delete method deletes the current record in the recordset. When a record is deleted, the current record pointer becomes invalid. If there is no current record when the Delete method is used, a trappable error occurs.
  
  This method is not available for snapshot-type recordsets.
  
  

The following example generates a runtime error because there is no valid record pointer:

If rstitles.RecordCount = 0 Then

    rstitles.DELETE

End If

• Edit. The Edit method prepares a existing record for modification. If the recordset is repositioned before the Update method is used, the changes are lost. Record locking defaults to pessimistic unless you set the LockEdits property to FALSE. Once the record has been edited and updated, it remains the current record.
  
  This method is not available for snapshot-type recordsets.
  
  

The following example edits an existing record:

' set optimistic locking

rstitles.lockedit= false

' add a new record

rstitles.edit

rstitles("pubid")= 6

rstitles.("title") = "New Book "

' Update the new record

addnew.update

• FillCache. The FillCache method is used to fill the previously allocated cache of the recordset with records from the database. When using the FillCache method, you must specify the number of records to retrieve as well as the record number at which the retrieval is to begin. The FillCache method applies only to ODBC-type data sources.
  
  This method is not available for table-type or snapshot-type recordsets.
  
  
• FindFirst. The FindFirst method is used to search forward from the first record in a recordset to find the first record that meets the specified criteria. If the Find method locates the record, the NoMatch property is set to FALSE and the found record is made the current record. If no match is found, the record pointer is positioned to the first record in the recordset and the NoMatch property is set to TRUE. The FindFirst criteria expression works in the same way as the WHERE clause in an SQL statement.
  
  This method is not available for table-type recordsets.
  
  

The following example locates the first record in the recordset where pubid equals 13.

dstitle.findfirst "pubid= 13"

if nomatch= false

    ' the record was found

else

    ' the record was not found

endif

• FindLast. The FindLast method is used to search forward from the first record to locate the last record in a recordset that meets the specified criteria. If the Find method locates the record, the NoMatch property is set to FALSE and the found record is made the current record. If there is no match, the record pointer is positioned to the first record in the recordset and the NoMatch property is set to TRUE. The FindLast criteria expression works as the WHERE clause in an SQL statement.
  
  This method is not available for table-type recordsets.
  
  
• FindNext. The FindNext method is used to search forward from the current record to locate the next record in a recordset that meets the specified criteria. If the Find method locates the record, the NoMatch property is set to FALSE and the found record becomes the current record. If there is no match, the record pointer is positioned to the first record in the recordset and the NoMatch property is set to TRUE. The FindNext criteria expression works the same way as the WHERE clause in an SQL statement.
  
  This method is not available for table-type recordsets.
  
  
• FindPrevious. The FindPrevious method is used to search backwards from the current record to locate the first record in a recordset that meets the specified criteria. If the Find method locates the record, the NoMatch property is set to FALSE and the found record is made the current record. If there is no match, the record pointer is positioned to the first record in the recordset and the NoMatch property is set to TRUE. The FindLast criteria expression works the same way as the WHERE clause in an SQL statement.
  
  This method is not available for table-type recordsets.
  
  

Note

If any of the Find methods are used on an empty recordset, a trappable error occurs.

• Move. The Move method is used to move a specified number or records from either the beginning of the recordset or from a specified starting point. If the EOF property is TRUE, any attempt to move forward causes a trappable error. If the BOF property is TRUE, any attempt to move backward causes a trappable error.
  
  
• MoveFirst. The MoveFirst method moves to the first record in a recordset.
  
  
• MoveLast. The MoveLast method moves to the last record in a recordset.
  
  
• MoveNext. The MoveNext method moves to the next record in a recordset. If the EOF property is TRUE before the MoveNext method is attempted, a trappable error occurs.
  
  
• MovePrevious. The MovePrevious method moves to the previous record in a recordset. If the BOF property is TRUE before the MovePrevious method is attempted, a trappable error occurs.
  
  

Note

If any of the Move methods are used on an empty recordset, a trappable error occurs.

• OpenRecordset. The OpenRecordset method creates a new Recordset object from an existing Recordset object. Unlike the OpenRecordset method of a Database object, you cannot use the OpenRecordset method of a Recordset object to specify the source of the new Recordset object. Instead, all records contained in the existing recordset are returned in the new recordset.
  
  

The OpenRecordset method has the following parameters:

Parameter

Description

The following example creates a new recordset from an existing recordset:

Dim wstitle As Workspace

Dim dbbiblio As Database

Dim proptext As Property

Dim rstitle As Recordset

Dim rsnewAs Recordset

' open the Workspace

Set wstitle = DBEngine.CreateWorkspace("title", "Admin", "")

' open the Database

Set dbbiblio = wstitle.OpenDatabase("c:\vb40\biblio.mdb", False, False)

' Open the original recordset

set rstitle= wstitle.openrecordset("select * from titles",dbOpenDynaset)

' Open the RecordSet

Set rsnew = qrynew.OpenRecordset(dbOpenTable)

• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  
• ReQuery. The ReQuery method re-executes the query originally used to create the recordset.
  
  This method is not available for table-type recordsets.
  
  
• Seek. The Seek method is used to locate the first record in an indexed table-type recordset that matches the specified criteria. The specified criteria must mach the index expression used to create the index.
  
  This method is not available for Dynaset-type or snapshot-type recordsets.
  
  
• Update. The Update method is used to update any changes made to a record after the Edit or AddNew method was used on the recordset. If the Update method is used before an AddNew or Edit method, a trappable error occurs. During the Update method, validation rules are checked and errors are indicated if the validation fails.
  
  To ensure that all locks are removed after the update, use the IDLE method of the DBEngine object with the FreeLocks parameter.
  
  

Relation Object/Collection

The Relation collection allows you to define table-level relations in your database. The
Relation object contains and defines the Field object and collection.

Relation Object/Collection Properties

The following properties are available to the Relation object/collection:

• Attributes. The Attributes property is read/write at runtime until the relation has been added to the Relation collection. Once the Relation object is added to the collection, the Attributes property becomes read-only.
  
  The following constants are available for use with the Attributes property:
  

Constant

Description

• Count. The Count property is used to indicate the number of Relation objects contained in the Relation collection. If Count equals 0, there are no relations contained in the Relation collection.
  
  
• ForeignTable. The ForeignTable property is read/write at runtime until the relation has been added to the Relation collection. Once the Relation object is added to the collection, the ForeignTable property becomes read-only. The ForeignTable parameter is the child table in the relation.
  
  
• Name. The Name property is read/write at runtime until the relation has been added to the Relation collection. Once the Relation object is added to the collection, the Name property becomes read-only. The Name property indicates how the relation is to be addressed in the application.
  
  
• Table. The Table property is read/write at runtime until the relation has been added to the Relation collection. Once the Relation object is added to the collection, the Table property becomes read-only. The Table parameter is the parent table in the relation.
  
  

Relation Object/Collection Methods

The following methods are available to the Relation object/collection:

• Append. The Append method adds a new Relation object to the Relation collection.
  
  
• CreateField. The CreateField method is used to define the characteristics of a field that will become part of the relation. The parameters for the CreateField method are identical to those of the CreateField method of the Index object, described earlier in this chapter.
  
  
• Delete. The Delete method removes a Relation object from the Relation collection.
  
  
• Refresh. The Refresh method ensures that the collection is complete and up to date.
  
  

Error Object/Collection

The Error collection allows you to set and return error codes and error messages throughout your application.

Error Object/Collection Properties

The following properties are available to the Error object/collection:

• Count. The Count property indicates the number of errors contained in the Error collection. If Count equals 0, there are no errors contained in the Error collection.
  
  
• Description. The Description property specifies a text description that is displayed to the user when the error occurs.
  
  
• HelpContext and HelpFile. If valid HelpFile and HelpContext IDs are specified, the help file is opened and positioned to the topic defined by the HelpContext ID when the error occurs.
  
  
• Number. The Number property specifies an error number to identify the error that has occurred.
  
  
• Source. The Source property indicated to the user where in the application the error occurred.
  
  

Error Object/Collection Methods

The following methods are available to the Error object/collection:

• Clear. The Clear method is used in order to clear all of the properties associated with the Error object.
  
  
• Raise. The Raise method is used in order to indicate an error at runtime.
  
  The Raise method has the following parameters:
  

Parameter

Description

Summary

This chapter covered the various properties, methods, and events of the DAO. The examples provided have shown how the DAO can be used in order to manipulate the data stored in databases, as well as the underlying structure of the database.